-- DWS层营销活动主题相关表
-- 用户启动
create external table if not exists dws_nshop.dws_nshop_ulog_launch
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    launch_count int comment '启动次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_launch/';
-- 用户启动7day
create external table if not exists dws_nshop.dws_nshop_ulog_launch_7d
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    launch_count int comment '启动次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_launch_7d/';
-- 用户浏览
create external table if not exists dws_nshop.dws_nshop_ulog_view
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    view_count   int comment '浏览次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_view/';
create external table if not exists dws_nshop.dws_nshop_ulog_view_7d
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    view_count   int comment '浏览次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_view_7d/';
create external table if not exists dws_nshop.dws_nshop_ulog_search
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    search_count int comment '搜索次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_search/';
create external table if not exists dws_nshop.dws_nshop_ulog_search_7d
(
    user_id      string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    search_count int comment '搜索次数'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_search_7d/';
create external table if not exists dws_nshop.dws_nshop_ulog_comment
(
    user_id              string comment '用户id',
    device_num           string comment '设备号',
    device_type          string comment '设备类型',
    os                   string comment '手机系统',
    os_version           string comment '手机系统版本',
    manufacturer         string comment '手机制造商',
    carrier              string comment '电信运营商',
    network_type         string comment '网络类型',
    area_code            string comment '地区编码',
    comment_count        int comment '关注次数',-- 不去重
    comment_target_count int comment '关注产品次数',--去重
    ct                   bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_comment/';
create external table if not exists dws_nshop.dws_nshop_ulog_comment_7d
(
    user_id              string comment '用户id',
    device_num           string comment '设备号',
    device_type          string comment '设备类型',
    os                   string comment '手机系统',
    os_version           string comment '手机系统版本',
    manufacturer         string comment '手机制造商',
    carrier              string comment '电信运营商',
    network_type         string comment '网络类型',
    area_code            string comment '地区编码',
    comment_count        int comment '关注次数',
    comment_target_count int comment '关注产品次数',
    ct                   bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_ulog_comment_7d/';

-- 用户交易宽表
create external table if not exists dws_nshop.dws_nshop_user_orders
(
    user_id          string comment '用户id',
    customer_natives string comment '所在区域',
    orders_count     int comment '订单数量',
    orders_pay       DECIMAL(10, 1) comment '订单金额',
    orders_shipping  DECIMAL(10, 1) comment '订单运费金额',
    orders_district  DECIMAL(10, 1) comment '订单优惠金额',
    ct               bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_orders/';
-- 用户交易宽表7day
create external table if not exists dws_nshop.dws_nshop_user_orders_7d
(
    user_id         string comment '用户id',
    area_code       string comment '地区编码',
    orders_count    int comment '订单数量',
    orders_pay      DECIMAL(10, 1) comment '订单金额',
    orders_shipping DECIMAL(10, 1) comment '订单运费金额',
    orders_district DECIMAL(10, 1) comment '订单优惠金额',
    ct              bigint comment '产生时间'
) partitioned by (bdp_week string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_orders_7d/';

-- 用户投诉订单宽表
create external table if not exists dws_nshop.dws_nshop_user_complainant
(
    user_id              string comment '用户id',
    area_code            string comment '地区编码',
    compl_orders_count   int comment '订单数量',
    compl_orders_pay     DECIMAL(10, 1) comment '订单金额',
    compl_supplier_count int comment '商家数量',
    ct                   bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_complainant/';
-- 用户投诉订单宽表7day
create external table if not exists dws_nshop.dws_nshop_user_complainant_7d
(
    user_id              string comment '用户id',
    area_code            string comment '地区编码',
    compl_orders_count   int comment '订单数量',
    compl_orders_pay     DECIMAL(10, 1) comment '订单金额',
    compl_supplier_count int comment '商家数量',
    ct                   bigint comment '产生时间'
) partitioned by (bdp_week string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_complainant_7d/';
-- 商家用户交互记录宽表
create external table if not exists dws_nshop.dws_nshop_supplier_user
(
    supplier_id       string comment '商家id',
    supplier_type     int comment '供应商类型：1.自营，2.官方 3其他',
    view_count        int comment '浏览次数',
    comment_users     int comment '关注人数',
    comment_area_code int comment '关注地区数量',
    ct                bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/supplier/dws_nshop_supplier_user/';
-- 商家用户交互记录宽表7day
create external table if not exists dws_nshop.dws_nshop_supplier_user_7d
(
    supplier_id       string comment '商家id',
    supplier_type     int comment '供应商类型：1.自营，2.官方 3其他',
    view_count        int comment '浏览次数',
    comment_users     int comment '关注人数',
    comment_area_code int comment '关注地区数量',
    ct                bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/supplier/dws_nshop_supplier_user_7d/';
-- 商家日流水宽表
create external table if not exists dws_nshop.dws_nshop_supplier_sales
(
    supplier_id            string comment '商家id',
    supplier_type          int comment '供应商类型：1.自营，2.官方 3其他',
    sales_users            int comment '购物人数',
    sales_users_area       int comment '购物地区数量',
    sales_orders           int comment '购物订单数',
    salaes_orders_pay      DECIMAL(10, 1) comment '订单金额',
    salaes_orders_district DECIMAL(10, 1) comment '订单优惠金额',
    ct                     bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/supplier/dws_nshop_supplier_sales/';
-- 商家日流水宽表7day
create external table if not exists dws_nshop.dws_nshop_supplier_sales_7d
(
    supplier_id            string comment '商家id',
    supplier_type          int comment '供应商类型：1.自营，2.官方 3其他',
    sales_users            int comment '购物人数',
    sales_users_area       int comment '购物地区数量',
    sales_orders           int comment '购物订单数',
    salaes_orders_pay      DECIMAL(10, 1) comment '订单金额',
    salaes_orders_district DECIMAL(10, 1) comment '订单优惠金额',
    ct                     bigint comment '产生时间'
) partitioned by (bdp_week string)
    stored as parquet
    location '/data/nshop/dws/supplier/dws_nshop_supplier_sales_7d/';
-- 广告投放用户宽表
create external table if not exists dws_nshop.dws_nshop_release_user
(
    release_sources      string comment '投放渠道',
    release_category     string comment '投放浏览产品分类',
    release_users        int comment '投放浏览用户数',
    release_product_page int comment '投放浏览产品页面数',
    ct                   bigint comment '创建时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/release/dws_nshop_release_user/';
-- 用户营销活动宽表
create external table if not exists dws_nshop.dws_nshop_user_release
(
    user_id      string comment '用户id',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    source_count int comment '投放来源数量',
    ct           bigint comment '产生时间'
) partitioned by (bdp_day string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_release/';
-- 用户营销活动宽表7day
create external table if not exists dws_nshop.dws_nshop_user_release_7d
(
    user_id      string comment '用户id',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    source_count int comment '投放来源数量',
    ct           bigint comment '产生时间'
) partitioned by (bdp_week string)
    stored as parquet
    location '/data/nshop/dws/user/dws_nshop_user_release_7d/';